﻿using System;
using System.Configuration;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;

namespace mojoPortal.Web.RS.AppCode.Lib
{
    public class RSDataLookupLoader : System.Web.UI.UserControl
    {
        public static void LoadMonthDDL(DropDownList ddlMonth)
        {
            var formatInfo = new System.Globalization.DateTimeFormatInfo();
            string[] monthName = formatInfo.MonthNames;
            ddlMonth.Items.Clear();
            ddlMonth.Items.Add("");
            for (int i = 0; i < monthName.Count() - 1; i++)
            {
                ddlMonth.Items.Add(monthName[i]);
            }
        }
        public static void LoadIntegerGenDDL(DropDownList ddlInteger, int start, int end)
        {
            ddlInteger.Items.Clear();
            ddlInteger.Items.Add("");
            for (int i = start; i <= end; i++)
            {
                ddlInteger.Items.Add(i.ToString());
            }
        }
        public static void LoadDigitGenDDL(DropDownList ddlDigit, double start, double end)
        {
            ddlDigit.Items.Clear();
            ddlDigit.Items.Add("");
            for (double i = start; i <= end; i = i + 0.5)
            {
                ddlDigit.Items.Add(i.ToString());
            }
        }
        public static void LoadNewsLetterDDL(int companyid, DropDownList ddlNewsLetter, string itemIndexZeroText = "", string itemIndexZeroValue = "0")
        {
            SqlCommand myCommand;
            SqlDataReader myDataReader;
            String sqlSELECT = "SELECT * FROM rs_tblNewsLetter WHERE (IsTemplate=0 OR IsTemplate IS NULL) AND CompanyId=@CompanyId ORDER BY Title ASC";
            SqlParameter param;
            SqlConnection myConnection;

            myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings[RSCommonFunctions.GetRSCompanyConnectionString()].ToString());
            myCommand = new SqlCommand(sqlSELECT, myConnection);
            param = new SqlParameter();
            param.SqlDbType = SqlDbType.Int;
            param.ParameterName = "@companyid";
            param.Value = companyid;
            myCommand.Parameters.Add(param);

            myConnection.Open();
            myDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
            ddlNewsLetter.Items.Clear();
            ddlNewsLetter.DataSource = myDataReader;
            ddlNewsLetter.DataTextField = "Title";
            ddlNewsLetter.DataValueField = "NewsLetterId";
            ddlNewsLetter.DataBind();
            ddlNewsLetter.Items.Insert(0, new ListItem(itemIndexZeroText, itemIndexZeroValue));
            myConnection.Close();
        }
        public static void LoadNewsLetterTemplateDDL(int companyid, DropDownList ddlNewsLetter, string itemIndexZeroText = "", string itemIndexZeroValue = "0")
        {
            SqlCommand myCommand;
            SqlDataReader myDataReader;
            String sqlSELECT = "SELECT * FROM rs_tblNewsLetter WHERE IsTemplate=1 AND CompanyId=@CompanyId ORDER BY Title ASC";
            SqlParameter param;
            SqlConnection myConnection;

            myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings[RSCommonFunctions.GetRSCompanyConnectionString()].ToString());
            myCommand = new SqlCommand(sqlSELECT, myConnection);
            param = new SqlParameter();
            param.SqlDbType = SqlDbType.Int;
            param.ParameterName = "@companyid";
            param.Value = companyid;
            myCommand.Parameters.Add(param);

            myConnection.Open();
            myDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
            ddlNewsLetter.Items.Clear();
            ddlNewsLetter.DataSource = myDataReader;
            ddlNewsLetter.DataTextField = "Title";
            ddlNewsLetter.DataValueField = "NewsLetterId";
            ddlNewsLetter.DataBind();
            ddlNewsLetter.Items.Insert(0, new ListItem(itemIndexZeroText, itemIndexZeroValue));
            myConnection.Close();
        }

        //#region Lookup methods
        public static void LoadTypeDDL(int companyid, string category, DropDownList ddlType, string itemIndexZeroText = "< Please select >", string itemIndexZeroValue = "0")
        {
            SqlCommand myCommand;
            SqlDataReader myDataReader;
            String sqlSELECT = "SELECT TypeId, TypeName, IsSelected FROM [rs_tblType] WHERE Category=@category AND CompanyID=@companyid ORDER BY TypeName ASC";
            SqlParameter param;
            SqlConnection myConnection;

            myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings[RSCommonFunctions.GetRSCompanyConnectionString()].ToString());
            myCommand = new SqlCommand(sqlSELECT, myConnection);

            param = new SqlParameter();
            param.SqlDbType = SqlDbType.Int;
            param.ParameterName = "@companyid";
            param.Value = companyid;
            myCommand.Parameters.Add(param);

            param = new SqlParameter();
            param.SqlDbType = SqlDbType.NVarChar;
            param.ParameterName = "@category";
            param.Value = category;
            myCommand.Parameters.Add(param);

            myConnection.Open();

            myDataReader = myCommand.ExecuteReader(CommandBehavior.Default);
            ddlType.Items.Clear();
            ddlType.Items.Insert(0, new ListItem(itemIndexZeroText, itemIndexZeroValue));
            int i = 1;
            while (myDataReader.Read())
            {
                ddlType.Items.Insert(i, new ListItem(myDataReader["TypeName"].ToString(), myDataReader["TypeId"].ToString()));
                if (Convert.ToBoolean(myDataReader["IsSelected"].ToString()) == true)
                {
                    ddlType.SelectedValue = myDataReader["TypeId"].ToString();
                }
                i = i + 1;
            }
            myConnection.Close();
        }


        public static void LoadStatusDDL(int companyid, string category, DropDownList ddlStatus, string itemIndexZeroText = "< Please select >", string itemIndexZeroValue = "0")
        {
            SqlCommand myCommand;
            SqlDataReader myDataReader;
            String sqlSELECT = "SELECT StatusID, StatusName, IsSelected FROM [rs_tblStatus] WHERE CompanyID=@companyid AND Category=@Category ORDER BY StatusName ASC";
            SqlParameter param;
            SqlConnection myConnection;

            myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings[RSCommonFunctions.GetRSCompanyConnectionString()].ToString());
            myCommand = new SqlCommand(sqlSELECT, myConnection);
            param = new SqlParameter();
            param.SqlDbType = SqlDbType.Int;
            param.ParameterName = "@companyid";
            param.Value = companyid;
            myCommand.Parameters.Add(param);

            param = new SqlParameter();
            param.SqlDbType = SqlDbType.NVarChar;
            param.ParameterName = "@category";
            param.Value = category;
            myCommand.Parameters.Add(param);

            myConnection.Open();
            myDataReader = myCommand.ExecuteReader(CommandBehavior.Default);
            ddlStatus.Items.Clear();
            ddlStatus.Items.Insert(0, new ListItem(itemIndexZeroText, itemIndexZeroValue));
            int i = 1;
            while (myDataReader.Read())
            {
                ddlStatus.Items.Insert(i, new ListItem(myDataReader["StatusName"].ToString(), myDataReader["StatusId"].ToString()));
                if (Convert.ToBoolean(myDataReader["IsSelected"].ToString()) == true)
                {
                    ddlStatus.SelectedValue = myDataReader["StatusId"].ToString();
                }
                i = i + 1;
            }
            myConnection.Close();
        }

        public static void LoadCodeDDL(int companyid, string category, DropDownList ddlCode, string itemIndexZeroText = "< Please select >", string itemIndexZeroValue = "0")
        {
            SqlCommand myCommand;
            SqlDataReader myDataReader;
            String sqlSELECT = "SELECT CodeId, CodeName, IsSelected FROM [rs_tblCode] WHERE Category=@category AND CompanyID=@companyid ORDER BY CodeName ASC";
            SqlParameter param;
            SqlConnection myConnection;

            myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings[RSCommonFunctions.GetRSCompanyConnectionString()].ToString());
            myCommand = new SqlCommand(sqlSELECT, myConnection);

            param = new SqlParameter();
            param.SqlDbType = SqlDbType.Int;
            param.ParameterName = "@companyid";
            param.Value = companyid;
            myCommand.Parameters.Add(param);

            param = new SqlParameter();
            param.SqlDbType = SqlDbType.NVarChar;
            param.ParameterName = "@category";
            param.Value = category;
            myCommand.Parameters.Add(param);

            myConnection.Open();

            myDataReader = myCommand.ExecuteReader(CommandBehavior.Default);
            ddlCode.Items.Clear();
            ddlCode.Items.Insert(0, new ListItem(itemIndexZeroText, itemIndexZeroValue));
            int i = 1;
            while (myDataReader.Read())
            {
                ddlCode.Items.Insert(i, new ListItem(myDataReader["CodeName"].ToString(), myDataReader["CodeID"].ToString()));
                if (Convert.ToBoolean(myDataReader["IsSelected"].ToString()) == true)
                {
                    ddlCode.SelectedValue = myDataReader["CodeID"].ToString();
                }
                i = i + 1;
            }
            myConnection.Close();
        }

        public static void LoadPublicTypeDDL(string category, DropDownList ddlIndustryType, string itemIndexZeroText = "< Please select >", string itemIndexZeroValue = "0")
        {
            SqlCommand myCommand;
            SqlDataReader myDataReader;
            String sqlSELECT = "SELECT TypeId, TypeName, IsSelected FROM [rs_tblType] WHERE Category=@category ORDER BY TypeName ASC";
            SqlParameter param;
            SqlConnection myConnection;

            myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["ReadyServiceConnectionStringMain"].ToString());
            myCommand = new SqlCommand(sqlSELECT, myConnection);

            param = new SqlParameter();
            param.SqlDbType = SqlDbType.NVarChar;
            param.ParameterName = "@category";
            param.Value = category;
            myCommand.Parameters.Add(param);

            myConnection.Open();

            myDataReader = myCommand.ExecuteReader(CommandBehavior.Default);
            ddlIndustryType.Items.Clear();
            ddlIndustryType.Items.Insert(0, new ListItem(itemIndexZeroText, itemIndexZeroValue));
            int i = 1;
            while (myDataReader.Read())
            {
                ddlIndustryType.Items.Insert(i, new ListItem(myDataReader["TypeName"].ToString(), myDataReader["TypeID"].ToString()));
                if (Convert.ToBoolean(myDataReader["IsSelected"].ToString()) == true)
                {
                    ddlIndustryType.SelectedValue = myDataReader["TypeID"].ToString();
                }
                i = i + 1;
            }
            myConnection.Close();
        }
        public static void LoadPublicStatusDDL(string category, DropDownList ddlStatus, string itemIndexZeroText = "< Please select >", string itemIndexZeroValue = "0")
        {
            SqlCommand myCommand;
            SqlDataReader myDataReader;
            String sqlSELECT = "SELECT StatusID, StatusName, IsSelected FROM [rs_tblStatus] WHERE Category=@Category ORDER BY StatusName ASC";
            SqlParameter param;
            SqlConnection myConnection;

            myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["ReadyServiceConnectionStringMain"].ToString());
            myCommand = new SqlCommand(sqlSELECT, myConnection);

            param = new SqlParameter();
            param.SqlDbType = SqlDbType.NVarChar;
            param.ParameterName = "@category";
            param.Value = category;
            myCommand.Parameters.Add(param);

            myConnection.Open();
            myDataReader = myCommand.ExecuteReader(CommandBehavior.Default);
            ddlStatus.Items.Clear();
            ddlStatus.Items.Insert(0, new ListItem(itemIndexZeroText, itemIndexZeroValue));
            int i = 1;
            while (myDataReader.Read())
            {
                ddlStatus.Items.Insert(i, new ListItem(myDataReader["StatusName"].ToString(), myDataReader["StatusId"].ToString()));
                if (Convert.ToBoolean(myDataReader["IsSelected"].ToString()) == true)
                {
                    ddlStatus.SelectedValue = myDataReader["StatusId"].ToString();
                }
                i = i + 1;
            }
            myConnection.Close();
        }
        //#endregion
    }
}